/* eslint-disable quotes */
/* eslint-disable no-template-curly-in-string */
export const sqls = {
  alter: {
    sqlAlterAddAfter:
      "ALTER TABLE `${1:table}` ADD COLUMN `${2:col_name}` ${3:INT}${4: NOT NULL} AFTER `${5:col_name}`;",
    sqlAlterAddFirst:
      "ALTER TABLE `${1:table}` ADD COLUMN `${2:col_name}` ${3:INT}${4: NOT NULL} FIRST;",
    sqlAlterAdd:
      "ALTER TABLE `${1:table}` ADD COLUMN `${2:col_name}` ${3:INT}${4: NOT NULL};",
    sqlAlterAutoIncrement: "ALTER TABLE `${1:table}` AUTO_INCREMENT = ${2:1};",
    sqlAlterChange:
      "ALTER TABLE `${1:table}` CHANGE `${2:old_name}` `${3:new_name}` ${4:new_type}${5: NOT NULL};",
    sqlAlterDbCharset:
      "ALTER DATABASE `${1:table}` CHARACTER SET ${2:utf8} COLLATE ${3:utf8_unicode_ci};",
    sqlAlterDrop: "ALTER TABLE `${1:table}` DROP COLUMN `${2:col_name}`;",
    sqlAlterFkDrop:
      "ALTER TABLE `$1` DROP FOREIGN KEY `${2:fk_${3:the_table}_${4:far_table}}`;",
    sqlAlterFkIndex:
      "ALTER TABLE `$1` ADD INDEX `fk_$1_$3_idx` (`$2` ASC), ADD CONSTRAINT `fk_${1:the_table}_$3` FOREIGN KEY (`${2:foreign_key}`) REFERENCES `${3:far_table}` (`${4:far_key}`) ON DELETE NO ACTION ON UPDATE CASCADE",
    sqlAlterFk:
      "ALTER TABLE `$1` ADD CONSTRAINT `fk_${1:the_table}_$3` FOREIGN KEY (`${2:foreign_key}`) REFERENCES `${3:far_table}` (`${4:far_key}`) ON DELETE NO ACTION ON UPDATE CASCADE",
    sqlAlterIndexDrop: "ALTER TABLE `${1:table}` DROP INDEX `${3:index_name}`;",
    sqlAlterIndex:
      "ALTER TABLE `${1:table}` ADD ${2:INDEX} `${3:index_name}`(`${4:field}` ASC$5);",
    sqlAlterModify:
      "ALTER TABLE `${1:table}` MODIFY `${2:field}` ${3:NEW-FIELD-TYPE}${4: NOT NULL} ${5:AFTER `${6:field}`};",
    sqlAlterSort: "ALTER TABLE `${1:table}` ORDER BY `${2:field}`;",
    sqlAlterTableCharset:
      "ALTER TABLE `${1:table}` CHARACTER SET ${2:utf8} COLLATE ${3:utf8_unicode_ci};",
    sqlAlterTableRename: "ALTER TABLE `${1:table}` RENAME TO `${2:new_name}`;",
    sqlAlterUniqueKey:
      "ALTER TABLE `${1:table_name}` ADD UNIQUE `unique_${5:index_name}` (`${11:column_1}`${15:, `${18:column_2}`});",
  },
  constructions: {
    sqlCaseCond:
      "CASE ${1:-- Example: -- -------------------------------------------------------------------------- -- WHEN `my_var` = 5 THEN SELECT 'case equals 5'; -- -- WHEN `my_var` > 5 THEN BEGIN -- SELECT 'case bigger than 5'; -- END; -- -- ELSE SELECT 'case default'; -- -------------------------------------------------------------------------- -- you can use snippets for create a case -- s-case-w - When -- s-case-wb - When Block }${2:ELSE $0;} END CASE;",
    sqlCaseValue:
      "CASE `${2:var_name}` ${1:-- Example: -- -------------------------------------------------------------------------- -- WHEN 'my_val' THEN SELECT 'case my_val'; -- -- WHEN 'my_val_2' THEN BEGIN -- SELECT 'case my_val_2'; -- END; -- -- ELSE SELECT 'case default'; -- -------------------------------------------------------------------------- -- you can use snippets for create a case -- s--case-w - When -- s--case-wb - When Block }${3:ELSE $0;} END CASE;",
    sqlCaseWhenBlockCond:
      "WHEN `${1:var_name}` ${2:=} '${3:value}' THEN BEGIN ${0:-- SELECT 'case $3'}; END;",
    sqlCaseWhenBlockValue:
      "WHEN '${1:value}' THEN BEGIN ${0:-- SELECT 'case $1'}; END;",
    sqlCaseWhenCond:
      "WHEN `${1:var_name}` ${2:=} '${3:value}' THEN ${0:-- SELECT 'case $3'};",
    sqlCaseWhenValue: "WHEN '${1:value}' THEN ${0:-- SELECT 'case $1'};",
    sqlCursor:
      "DECLARE `${5:var_1}` ${7:VARCHAR(255)}; DECLARE `${10:done}` INT DEFAULT 0; DECLARE `${30:cur}` CURSOR FOR ${40:SELECT `User` FROM `mysql`.`user`}; DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET `${10:done}` = 1; OPEN `${30:cur}`; REPEAT FETCH `${30:cur}` INTO `${5:var_1}`; IF NOT `${10:done}` THEN ${0:-- Your code ... SELECT `${5:var_1}`; } END IF; UNTIL `${10:done}` END REPEAT; CLOSE `${30:cur}`;",
    sqlDeclareCursor: "DECLARE `${1:cursor_name}` FOR ${0:-- SELECT ...};",
    sqlDeclareDefault: "DECLARE `${1:var_name}` ${2:INT} DEFAULT '${3:value}';",
    sqlDeclareHandlerSqlstate:
      "DECLARE CONTINUE HANDLER FOR SQLSTATE '${1:02000}' ${2:SET `done` = 1};",
    sqlDeclareStringDefault:
      "DECLARE `${1:var_name}` VARCHAR(${2:255}) DEFAULT '${3:value}';",
    sqlDeclareString: "DECLARE `${1:var_name}` VARCHAR(${2:255});",
    sqlDeclare: "DECLARE `${1:var_name}` ${2:INT};",
    sqlIfElse: "IF `${1:var}` ${2:=} ${3:value} THEN ${4:-- body} ELSE END IF;",
    sqlIf: "IF `${1:var}` ${2:=} ${3:value} THEN ${4:-- body} END IF;",
    sqlLoop:
      "`${1:label_name}`: LOOP SET `${10:var_name}` = `${10}` ${11:+} ${12:1}; ${0:-- Your code ...} IF `${10}` ${20:>=} ${22:10} THEN LEAVE `${1:label_name}`; END IF; END LOOP `${1:label_name}`;",
    sqlRepeatBlock:
      "REPEAT BEGIN ${0:-- Your code ...} END; UNTIL `${1:var_name}` ${2:>} ${3:10} END REPEAT;",
    sqlRepeat:
      "REPEAT ${0:/* Your code ... */}; UNTIL `${1:var_name}` ${2:>} ${3:10} END REPEAT;",
  },
  db: {
    sqlDbDrop: "DROP DATABASE `${1:db_name}`;",
    sqlDb:
      "CREATE DATABASE `${1:db_name}` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;",
  },
  delete: {
    sqlDelete: "DELETE FROM `${1:table}`${2: WHERE `$3` = '$4'};",
  },
  functions: {
    sqlFunctionDrop: "DROP FUNCTION IF EXISTS `${1:name}`;",
    sqlFunctionList:
      "${1:SELECT `name`, `param_list`, `comment` FROM `mysql`.`proc` WHERE `db`=DATABASE() AND `type` = 'FUNCTION';}",
    sqlFunctionReplace:
      "DROP FUNCTION IF EXISTS `${1:function_name}`; CREATE FUNCTION `${1}`(`${2:arg_name}` ${3:INT}) RETURNS ${4:INT} BEGIN $5 END;",
    sqlFunction:
      "CREATE FUNCTION `${1:function_name}`(`${2:arg_name}` ${3:INT}) RETURNS ${4:INT} BEGIN $5 END;",
  },
  insert: {
    sqlInsertOne: "INSERT INTO `$1` SET `${2:name}` = '${3:value}'${4:, };",
    sqlInsertSelect:
      "INSERT INTO `${1:table}` SELECT ${2:*} FROM ${3:`${4:database}`.}`${5:$1}`;",
    sqlInsert: "INSERT INTO `$1`(`$2`) VALUES ($3)${4:, };",
  },
  messages: {},
  other: {
    sqlDelimiter: "DELIMITER ${1:\\$\\$} $2 $1 DELIMITER ;",
    sqlUtc: "SELECT UNIX_TIMESTAMP(NOW());",
  },
  privileges: {
    sPrivAddAll:
      "GRANT ALL PRIVILEGES ON *.* TO '${4:user_name}'@'${5:localhost}';",
    sPrivAdd:
      "GRANT ${1:ALL} PRIVILEGES ON `${2:db_name}`.`${3:table_name}` TO '${4:user_name}'@'${5:localhost}';",
    sPrivDropAll: "REVOKE ALL ON *.* FROM '${4:user_name}'@'${5:localhost}';",
    sPrivDrop:
      "REVOKE ${1:ALL} ON `${2:db_name}`.`${3:table_name}` FROM '${4:user_name}'@'${5:localhost}';",
    sPrivList: "SHOW GRANTS FOR '${1:user_name}'@'${2:localhost}'\\G",
    sPrivRefresh: "${0:FLUSH PRIVILEGES;}",
  },
  procedures: {
    sqlProcedureDrop: "DROP PROCEDURE IF EXISTS `${1:name}`;",
    sqlProcedureList:
      "${1:SELECT `name`, `param_list`, `comment` FROM `mysql`.`proc` WHERE `db`=DATABASE() AND `type` = 'PROCEDURE';}",
    sqlProcedureReplace:
      "DROP PROCEDURE IF EXISTS `${1:procedure_name}`; CREATE PROCEDURE `${1}`( `${2:arg_name}` ${3:INT}${4:,} ) BEGIN $5 END;",
    sqlProcedure:
      "CREATE PROCEDURE `${1:procedure_name}`( `${2:arg_name}` ${3:INT}${4:,} ) BEGIN $5 END;",
  },
  queryOptions: {
    sqlAndExpression: "AND ( `$1` ${2:=} '$3' )",
    sqlAnd: "AND ( $1 )",
    sqlEntityAlias: "`$1`.`$2` AS `$3`",
    sqlEntityCommaAlias: ", `$1`.`$2` AS `$3`",
    sqlEntityComma: ", `$1`.`$2`",
    sqlEntity: "`$1`.`$2`",
    sqlExpression: "( `$1` ${2:=} '$3' )",
    sqlFrom: "FROM `$1` AS `$2`",
    sqlGroupBy: "GROUP BY `$1`",
    sqlHaving: "HAVING ( `$1` ${2:=} '$3' )",
    sqlJoinInner:
      "INNER JOIN `$1` AS `${2:${1/(.)[^_]*(?:_(.))?.*/$1$2/}}` ON ( `${2:${1/(.)[^_]*(?:_(.))?.*/$1$2/}}`.`${4:id}` = `$5`.`${6:id}` )",
    sqlJoinLeft:
      "LEFT JOIN `$1` AS `${2:${1/(.)[^_]*(?:_(.))?.*/$1$2/}}` ON ( `${2:${1/(.)[^_]*(?:_(.))?.*/$1$2/}}`.`${4:id}` = `$5`.`${6:id}` )",
    sqlJoinRight:
      "RIGHT JOIN `$1` AS `${2:${1/(.)[^_]*(?:_(.))?.*/$1$2/}}` ON ( `${2:${1/(.)[^_]*(?:_(.))?.*/$1$2/}}`.`${4:id}` = `$5`.`${6:id}` )",
    sqlLimit: "LIMIT ${1:0}, ${3:count}",
    sqlOrExpression: "OR ( `$1` ${2:=} '$3' )",
    sqlOr: "OR ( $1 )",
    sqlOrderBy: "ORDER BY `$1`${2: DESC}",
    sqlUnion: "( $1 ) UNION ( $2 )",
    sqlWhere: "WHERE ( `$1` ${2:=} '$3' )",
  },
  select: {
    sqlSelectCount: "SELECT COUNT(${1:*}) FROM `$2` AS `${2/(.).*/$1/}`",
    sqlSelectInline: "SELECT `${3/(.).*/$1/}`.$1 FROM `$3` AS `${3/(.).*/$1/}`",
    sqlSelectMax: "SELECT MAX(${1:id}) FROM `$2` AS `${2/(.).*/$1/}`",
    sqlSelectMin: "SELECT MIN(${1:id}) FROM `$2` AS `${2/(.).*/$1/}`",
    sqlSelectOne: "${5:SELECT * FROM `$1` WHERE `${2:id}` ${3:=} '$4'; }",
    sqlSelectString: "SELECT '${1:My String}';",
    sqlSelectVar: "SELECT `${1:var_name}`;",
    sqlSelect:
      "SELECT `${4:${3/(.).*/$1/}}`.${1:*} FROM `$3` AS `${4:${3/(.).*/$1/}}`",
  },
  show: {
    sqlShowColumns: "${2:SHOW FULL COLUMNS FROM `${1:table}`;}",
    sqlShowCreate: "${2:SHOW CREATE TABLE `${1:table}`;}",
    sqlShowIndex: "${2:SHOW INDEX FROM `${1:table}`;}",
    sqlShowRelations:
      "${0:SELECT CONCAT( `constraint_name`, ' : ', `table_name`, '.', `column_name`, ' -> ', `referenced_table_name`, '.', `referenced_column_name` ) AS `relations_list` FROM `information_schema`.`key_column_usage` WHERE `table_schema` = ${1:DATABASE()} AND `table_name` = '${2:table_name}' AND `referenced_table_name` IS NOT NULL; }",
  },
  table: {
    sqlFkIndex:
      "INDEX `fk_$1_$3_idx` (`$2` ASC), CONSTRAINT `fk_${1:the_table}_$3` FOREIGN KEY (`${2:foreign_key}`) REFERENCES `${3:far_table}` (`${4:far_key}`) ON DELETE NO ACTION ON UPDATE CASCADE",
    sqlFk:
      "CONSTRAINT `fk_${1:the_table}_$3` FOREIGN KEY (`${2:foreign_key}`) REFERENCES `${3:far_table}` (`${4:far_key}`) ON DELETE NO ACTION ON UPDATE CASCADE",
    sqlIndexText: "FULLTEXT INDEX `$1_idx` (`${1:column}` ASC$2)${3:, }",
    sqlIndex: "INDEX `$1_idx` (`${1:email}` ASC$2)${3:, }",
    sqlManyMany:
      "CREATE INDEX `$4_idx` ON `$1` (`$4` ASC); CREATE INDEX `$5_idx` ON `$2` (`$5` ASC); CREATE TABLE IF NOT EXISTS `${3:$1_$2}` ( `${1:table1}_${4:id}` INT UNSIGNED NOT NULL, `${2:table2}_${5:id}` INT UNSIGNED NOT NULL, PRIMARY KEY (`$1_$4`, `$2_$5`), INDEX `fk_$3_$1_$4_idx` (`$1_$4` ASC), INDEX `fk_$3_$2_$5_idx` (`$2_$5` ASC), CONSTRAINT `fk_$3_$1` FOREIGN KEY (`$1_$4`) REFERENCES `$1` (`$4`) ON DELETE ${6:NO ACTION} ON UPDATE CASCADE, CONSTRAINT `fk_$3_$2` FOREIGN KEY (`$2_$5`) REFERENCES `$2` (`$5`) ON DELETE $6 ON UPDATE CASCADE ) ENGINE = InnoDB;",
    sqlPrimaryKey: "PRIMARY KEY `pk_${1:id}`(`$1`)${2:, }",
    sqlTableDrop: "DROP TABLE IF EXISTS `$1`;",
    sqlTableReplace:
      "DROP TABLE IF EXISTS `${1:table_name}`; CREATE TABLE `$1` ( `${2:id}` INT UNSIGNED NOT NULL AUTO_INCREMENT, `${3:name}` ${4:VARCHAR(255)}, PRIMARY KEY `pk_$2`(`$2`) ) ENGINE = InnoDB;",
    sqlTableTemporaryReplace:
      "DROP TABLE IF EXISTS `${1:table_name}`; CREATE TEMPORARY TABLE IF NOT EXISTS `${1:table_name}` ( `${2:id}` INT UNSIGNED NOT NULL AUTO_INCREMENT, `${3:name}` ${4:VARCHAR(255)}, PRIMARY KEY `pk_$2`(`$2`) ) ENGINE = MEMORY;",
    sqlTableTemporary:
      "CREATE TEMPORARY TABLE IF NOT EXISTS `${1:table_name}` ( `${2:id}` INT UNSIGNED NOT NULL AUTO_INCREMENT, `${3:name}` ${4:VARCHAR(255)}, PRIMARY KEY `pk_$2`(`$2`) ) ENGINE = MEMORY;",
    sqlTable:
      "CREATE TABLE IF NOT EXISTS `${1:table_name}` ( `${2:id}` INT UNSIGNED NOT NULL AUTO_INCREMENT, `${3:name}` ${4:VARCHAR(255)}, PRIMARY KEY `pk_$2`(`$2`) ) ENGINE = InnoDB;",
    sqlUniqueKey: "UNIQUE KEY `unique_${1:id}`(`$1`$2)${3:, }",
  },
  triggers: {
    sqlTriggerDrop: "DROP TRIGGER IF EXISTS `${1:name}`;",
    sqlTriggerListDb:
      "SHOW TRIGGERS FROM `${1:full_db_name}` LIKE '${2:table_name}%'\\G",
    sqlTriggerList: "SHOW TRIGGERS LIKE '${2:table_name}%'\\G",
    sqlTriggerReplace:
      "DROP TRIGGER IF EXISTS `${1:trigger_name}`; CREATE TRIGGER `${1}` ${2:AFTER} ${3:INSERT} ON `${4:table_name}` FOR EACH ROW BEGIN ${5: -- Use NEW and OLD constants for access to row} END;",
    sqlTrigger:
      "CREATE TRIGGER `${1:trigger_name}` ${2:AFTER} ${3:INSERT} ON `${4:table_name}` FOR EACH ROW BEGIN ${5: -- Use NEW and OLD constants for access to row} END;",
  },
  update: {
    sqlUpdate: "UPDATE `${1:table}` SET `$2` = '$3'${4:,} WHERE `$5` = '$6';",
  },
  users: {
    sqlUserAddHash:
      "CREATE USER '${1:username}'@'${2:localhost}' IDENTIFIED BY PASSWORD '*832EB84CB764129D05D498ED9CA7E5CE9B8F83EB';",
    sqlUserAddPassword:
      "CREATE USER '${1:username}'@'${2:localhost}' IDENTIFIED BY '${3:password}';",
    sqlUserAdd: "CREATE USER '${1:username}'@'${2:localhost}';",
    sqlUserDrop: "DROP USER '${1:username}'@'${2:localhost}';",
    sqlUserList: "SELECT `user`, `host`, `password` FROM `mysql`.`user`;",
    sqlUserPwd:
      "SET PASSWORD ${1:FOR '${2:username}'@'${3:localhost}'} = PASSWORD('${4:password}');",
  },
};
